# -*- coding:UTF-8 -*-
import os
import re
import argparse


def output_result(log, re: str, re_all: str, out_path: str, type: int = 0):
    if type == 1:
        # 当type==1时，表示是insert语句的检查，日志及结果输出到script_insert中
        warning_info = "SCRIPT_INSERT_WARNING"
        out_file = "script_insert.txt"
        log_file = "script_insert.log"
    else:
        warning_info = "SCRIPT_WARNING"
        out_file = "script.txt"
        log_file = "script.log"

    if re:
        try:
            log.info("{warning_info}:{re}".format(warning_info=warning_info, re=re))
        except:
            print("{warning_info}:{re}".format(warning_info=warning_info, re=re))
    if os.path.isdir(out_path):
        # os.chdir(out_path)
        result = open("{out_path}/{out_file}".format(out_path=out_path, out_file=out_file), "w")
        result.write(re)
        result.close()
        result = open("{out_path}/{log_file}".format(out_path=out_path, log_file=log_file), "w")
        result.write(re_all + re)
        result.close()


def start(log=None, path_list=None, out_path="", **kwargs):
    # __config = configparser.ConfigParser()
    # __config.read('config.ini')
    # # 获得需要检测的路径
    # path = __config.get('pt', 'path')
    # dest = __config.get('pt', 'dest')
    try:
        log.info("%s" % path_list)
        log.info("%s" % out_path)
    except:
        print("%s" % path_list)
        print("%s" % out_path)

    files = []
    for (dirpath, dirnames, filenames) in os.walk(path_list):
        for filename in filenames:
            if filename.endswith(".sql"):
                # try:
                #     log.info("filename %s" % filename)
                #     log.info("filenane:%s%s"%(dirpath,filename))
                # except:
                #     print("filename %s" % filename)
                #     print("filenane:%s%s"%(dirpath,filename))
                files.append(dirpath + "/" + filename)
    if not files:
        try:
            log.info('不存在的目录')
        except:
            print('不存在的目录')
        return

    re = ""
    re_all = ""
    for f in files:
        re += check(f)
        re_all += f
    output_result(log, re, re_all, out_path)

    if kwargs.get("check_insert") == "true":
        # check insert 语句
        re = ""
        re_all = ""
        for f in files:
            re += check_insert_value(f, **kwargs)
            re_all += f
        output_result(log, re, re_all, out_path, 1)


def check(file):
    fo = open(file, "r", errors='ignore')
    content = fo.read()
    # 去除注释中的内容
    replace_comment = re.sub("--(.|[\r\n])*?\n", '', content)
    replace_result = re.sub("/\*(.|[\r\n])*?\*/", '', replace_comment)
    re1 = ""
    # 以分号为结束 分割文本
    # sentences = replace_result.split(";\n")
    sentences = re.split(";\s.*\n", replace_result)
    for s in sentences:
        s = s.replace("prompt update", "prompt update_t").replace("prompt delete", "prompt delete_t").replace(
            "prompt truncate", "prompt truncate_t").replace("prompt drop", "prompt drop_t")
        s = s.strip()
        if "update " in s and "merge into " not in s:
            if "where " not in s:
                re1 = re1 + file + "<脚本中存在update没有where的情况>\n"
            # 需要考虑update set (select)的情况 select 中可能存在where
            else:
                if "select" in s:
                    temp = s
                    select_num = (len(temp) - len(temp.replace("select", ""))) // 6
                    where_num = (len(temp) - len(temp.replace("where", ""))) // 5
                    if select_num == where_num:
                        re1 = re1 + file + "<脚本中存在：update语句中包含select语句，" \
                                           "且where数量与select数量相等，存在update没有where的可能>\n"
        if "delete " in s:
            if "where " not in s:
                re1 = re1 + file + "<脚本中存在delete没有where的情况>\n"
        if "truncate " in s:
            re1 = re1 + file + "<脚本中含有truncate>\n"
        if "drop " in s:
            re1 = re1 + file + "<脚本中含有drop>\n"
        if "merge into " in s:
            if "on " not in s:
                re1 = re1 + file + "<脚本中存在merge into没有on的情况>\n"

    fo.close()
    return re1


def check_insert_value(file, **kwargs):
    table = kwargs.get("table")
    values = kwargs.get("values")
    # 解析kwargs.get("values")，格式 column=value,column=value,column=value
    tmp_value_list = values.split(',')
    correct_value_dict = dict()
    for tmp_value in tmp_value_list:
        tmp = tmp_value.split('=')
        correct_value_dict[tmp[0]] = tmp[1]

    fo = open(file, "r", errors='ignore')
    content = fo.read()
    # 去除注释中的内容
    replace_comment = re.sub("--(.|[\r\n])*?\n", '', content)
    replace_result = re.sub("/\*(.|[\r\n])*?\*/", '', replace_comment)
    re1 = ""
    # 以分号为结束 分割文本
    # sentences = replace_result.split(";\n")
    sentences = re.split(";\s.*\n", replace_result)
    for s in sentences:
        s = s.strip()
        tmp_str = "insert"
        # 过滤出insert的语句,用lower()全部转换成小写，兼容大小写的情况
        if tmp_str.lower() in s.lower():
            accurate_table = " " + table + " "  # 排除table名是其他Table名的子串的情况
            database_table = "." + table + " "  # 兼容table名带数据库名的情况
            if accurate_table.lower() in s.lower() or database_table.lower() in s.lower():
                s = s.split(table + " ")[1]
                # 支持单表多属性的值判断
                for column in correct_value_dict.keys():
                    # 兼容大小写的情况
                    if column.lower() not in s.lower():
                        re1 = re1 + file + "<脚本中存在{tmp_str}未设置{column}的值的情况>\n".format(tmp_str=tmp_str, column=column)
                    else:
                        # insert into table() values ()
                        # 匹配第一个括弧中的内容，并做解析
                        col_match = re.search(r"\((.*?)\)", s, re.DOTALL)
                        index = -1
                        if col_match:
                            col_list = col_match.group(1).split(",")
                            for i in range(len(col_list)):
                                # 找到指定的column是写在第几个 - index
                                if col_list[i].strip().lower() == column.lower():
                                    # 兼容大小写的情况
                                    index = i
                                    break
                        else:
                            continue

                        # insert into table() values ()
                        # 就拿values后面的内容即可
                        value_match = s.split("values")[1]
                        if "'||'" in value_match:
                            value_match = value_match.replace("'||'", "")
                        # 匹配单引号中的内容以及null，兼容中文
                        pattern = re.compile(r"'([^'\\]*(?:\\.[^'\\]*)*)'|null", re.UNICODE)
                        values = [match.group(1) for match in re.finditer(pattern, value_match)]
                        if values[index] is None:
                            re1 = re1 + file + "<脚本中存在{tmp_str}但{column} != {correct_value}的情况," \
                                               "实际情况:{column} = null>\n".format(tmp_str=tmp_str,
                                                                                   column=column,
                                                                                   correct_value=correct_value_dict[column])
                        else:
                            c_value = values[index].strip().strip("'")
                            # 兼容大小写的情况
                            if c_value.lower() != correct_value_dict[column].lower():
                                re1 = re1 + file + "<脚本中存在{tmp_str}但{column} != {correct_value}的情况," \
                                                   "实际情况:{column} = {value}>\n".format(tmp_str=tmp_str,
                                                                                          column=column,
                                                                                          correct_value=correct_value_dict[column],
                                                                                          value=c_value)

    fo.close()
    return re1


def parse_args():
    parser = argparse.ArgumentParser(description='sql文件规则分析工具')
    parser.add_argument('-p', '--path_list', nargs='?', action='store', help='检查哪个目录下面的sql文件')
    parser.add_argument('-o', '--out_path', nargs='?', action='store', help='结果及日志输出路径')
    parser.add_argument('--check_insert', nargs='?', action='store', default='false',
                        help='是否需要check insert语句，默认为false')
    parser.add_argument('--table', nargs='?', action='store', help='check_insert=true时，检查哪张表的insert语句')
    parser.add_argument('--values', nargs='?', action='store',
                        help='check_insert=true时，设置 属性名=期望值,如column=value,column=value,column=value')

    args = parser.parse_args()

    if not args.path_list or not args.out_path:
        parser.print_help()
        print('\n必须指定检查目录path_list及输出路径out_path\n')
        exit(0)

    if args.check_insert == 'true' and (not args.table or not args.values):
        parser.print_help()
        print('\ncheck_insert=true时必须指定table和values\n')
        exit(0)

    if args.check_insert == 'true' and "=" not in args.values:
        parser.print_help()
        print('\nvalues的输入格式错误，正确格式:column=value,column=value,column=value\n')
        exit(0)

    return args


if __name__ == '__main__':
    args = parse_args()
    start(log=None, **args.__dict__)
